package cn.hc.tool.exec.controller;

import cn.hc.tool.exec.aspect.Administrators;
import cn.hc.tool.exec.bean.HcResult;
import cn.hc.tool.exec.service.HcBeanService;
import com.hc.json.adapter.Json;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author huangchao E-mail:fengquan8866@163.com
 * @version 创建时间：2024/9/24 19:42
 */
@Controller
@RequestMapping("/sql")
@Slf4j
@ResponseBody
@Administrators
public class HcSqlController {

    @Autowired
    private HcBeanService hcBeanService;

    /**
     * 执行sql语句
     *
     * @param sql  sql语句
     * @param type 默认查询1； 2：更新
     * @return 执行结果
     */
    @RequestMapping(value = {"/exec", "/exec.html", "/exec.json"}, method = {RequestMethod.GET, RequestMethod.POST}, produces = "application/json;charset=UTF-8")
    public String sql(@RequestParam(value = "sql") String sql, String dataSourceName, Integer type) throws SQLException {
        HcResult<Object> result = new HcResult<Object>();
        DataSource dataSource = hcBeanService.getBean(DataSource.class, dataSourceName);
        Connection con = dataSource.getConnection();

        try {
            con.setAutoCommit(false);
            PreparedStatement ps = con.prepareStatement(sql);

            try {
                log.error("执行sql : {}", sql);
                if (type != null && !type.equals(1)) {
                    int affect = ps.executeUpdate();
                    con.commit();
                    result.setData(affect);
                } else {
                    ResultSet rs = ps.executeQuery(sql);

                    try {
                        List<Map<String, Object>> tab = new ArrayList<Map<String, Object>>();
                        if (rs != null) {
                            ResultSetMetaData md = rs.getMetaData();
                            int count = md.getColumnCount();

                            while (rs.next()) {
                                Map<String, Object> row = new LinkedHashMap<String, Object>();

                                for (int i = 1; i <= count; ++i) {
                                    row.put(md.getColumnName(i), rs.getObject(i));
                                }

                                tab.add(row);
                            }
                        } else {
                            log.error("ResultSet is null！");
                        }

                        result.setData(tab);
                    } catch (Exception e2) {
                        throw e2;
                    } finally {
                        if (rs != null) {
                            try {
                                rs.close();
                            } catch (Exception e3) {
                                log.error("error in rs.close, {}", e3.getMessage());
                            }
                        }

                    }
                }

                result.setSuccess(true);
            } catch (Exception e) {
                throw e;
            } finally {
                if (ps != null) {
                    try {
                        ps.close();
                    } catch (Exception var60) {
                        log.error("error in ps.close, {}", var60.getMessage());
                    }
                }

            }
        } catch (Exception var66) {
            try {
                if (con != null) {
                    con.rollback();
                }
            } catch (SQLException var59) {
                log.warn("sql 回滚异常", var66);
            }

            log.warn("sql 执行异常", var66);
            result.setMessage("sql执行异常:" + sql);
            result.setSuccess(false);
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException var58) {
                log.warn("链接关闭异常", var58);
            }

        }

        return Json.toJson(result);
    }
}
